package server;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author eocandos
 */
class ConnectionDB {

    Connection con;
    Statement st;
    String sql;
    ArrayList<BeanLinks> links = new ArrayList<BeanLinks>();

    public ConnectionDB() throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");

        //Realizamos la conexion con nuestra BD
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/DBMyWeb1", "root", "cks183");

        //Preparamos la consulta
        st = con.createStatement();

    }

    public List<BeanLinks> consultar() {
        try {

            sql = "SELECT * FROM Links";
            //Realizamos la consulta
            ResultSet rs = st.executeQuery(sql);

            //System.out.print("Ok in the DB!");
            while (rs.next()) {

                BeanLinks bean = new BeanLinks();

                bean.setName(rs.getString(2));
                bean.setUrl(rs.getString(3));
                bean.setDate(rs.getString(4));
                bean.setDes(rs.getString(5));
                bean.setPos(rs.getString(6));
                bean.setTags(rs.getString(7));

                //Agregamos el objeto beans a nuestro array
                links.add(bean);
            }

        } catch (SQLException ex) {
            System.out.println("Error al hacer Select! " + ex.getMessage());
        }
        return links;
    }

    public BeanLinks buscar(String table, String id) {

        BeanLinks bean = new BeanLinks();

        try {
            sql = "select * from Links where id = " + id;
            //sql = "SELECT name, url, date, desc, pos, tags FROM Links WHERE id = '"+ id+ "'";
            //Realizamos la consulta

            PreparedStatement ps = con.prepareStatement(sql);
            //ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            //System.out.println("Ok!!!");

            while (rs.next()) {

                bean.setName(rs.getString(2));
                bean.setUrl(rs.getString(3));
                bean.setDate(rs.getString(4));
                bean.setDes(rs.getString(5));
                bean.setPos(rs.getString(6));
                bean.setTags(rs.getString(7));
            }

        } catch (Exception ex) {
            System.out.println("Error al hacer Select! " + ex.getMessage());
        }
        return bean;

    }

    // Guardar Nuevos Links
    public void insertDates(String name, String url, String date, String desc, String pos, String tags) {

        sql = "INSERT INTO  Links " + " VALUES(NULL ,  '" + name + "',  '" + url + "', '" 
                + date + "', '" + desc + "',  '" + pos + "',  '" + tags + "' " + ");";

        // sql = "INSERT INTO  Links "+" VALUES(NULL ,  'megan',  'www.megan.fox', 'xx', 'mamasita rica',  '5',  'cosita, rica, actriz' " +");";
        try {

            st.executeUpdate(sql);
            System.out.println("Datos insertados!! ");

        } catch (SQLException ex) {
            System.out.println("Error al insertar! " + ex.getMessage());
        }
    }

    public void setUpdate(String id, String name, String url, String date, String desc, String pos, String tags) {
        try {

            String sql = "UPDATE Links SET name='" + name + "',url='" + url + "',date='" + date 
                    + "',pos='" + pos + "',tags='" + tags + "' where id =" + id;
            //String sql = "UPDATE Links SET name='" + name + "',url='" + url + "',date='" + date 
            //        + "',pos='" + pos + "',tags='" + tags + "' WHERE id=" + id;
            
            PreparedStatement ps = con.prepareStatement(sql);
            
            ps.executeUpdate(sql);
            System.out.println("Actualizacion Exitosa");
        } catch (SQLException ex) {
            System.out.println("Error al Actualizar! " + ex.getMessage());
        }

    }

}
